/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controllers;

import Models.Wanted;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Pan
 */
public class WantedController {

    static Connection conn = DBUtility.openConnection();

    public static ArrayList<Wanted> GetWanteds() {
        try {
            ArrayList<Wanted> wantedList = new ArrayList<Wanted>();

            CallableStatement cs = conn.prepareCall("{call sp_GetWanteds}", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                //If there are result, the ResultSet is reseted, 
                //and each record will be added to the ArrayList and then get returned
                rs.beforeFirst();
                while (rs.next()) {
                    int _id = rs.getInt("WantedID");
                    wantedList.add(new Wanted(_id));
//                    criminalList.add(GetCriminalByID(_id));
                }
                return wantedList;
            }
        } catch (SQLException ex) {
            Logger.getLogger(Wanted.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public static boolean AddWanted(Wanted wanted) {
        CallableStatement cs;
        try {
            cs = conn.prepareCall("{call sp_AddWanted(?,?,?,?,?)}");

            cs.setInt("CriminalID", wanted.getRelatedCriminal().getID());
            java.sql.Date _sqlDate = new java.sql.Date(wanted.getWantedDate().getTime());
            cs.setDate("WantedDate", _sqlDate);
            cs.setInt("BountyMoney", wanted.getBountyMoney());
            cs.setString("SightingPlace", wanted.getSightingPlace());
            if (wanted.getSightingTime() == null) {
                cs.setDate("SightingTime", null);
            } else {
                _sqlDate = new java.sql.Date(wanted.getSightingTime().getTime());
                cs.setDate("SightingTime", _sqlDate);
            }
            if (cs.executeUpdate() > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(WantedController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }

    public static boolean EditWanted(Wanted wanted) {
        CallableStatement cs;
        try {
            cs = conn.prepareCall("{call sp_EditWanted(?,?,?,?,?)}");

            cs.setInt("BountyMoney", wanted.getBountyMoney());
            cs.setString("SightingPlace", wanted.getSightingPlace());
            if (wanted.getSightingTime() == null) {
                cs.setDate("SightingTime", null);
            } else {
                java.sql.Date _sqlDate = new java.sql.Date(wanted.getSightingTime().getTime());
                cs.setDate("SightingTime", _sqlDate);
            }
            cs.setBoolean("InEffect", wanted.isInEffect());
            cs.setInt("WantedID", wanted.getID());
            if (cs.executeUpdate() > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(WantedController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }

    public static ArrayList<Wanted> FilterWanted(HashMap parametersMap) {
        CallableStatement cs;
        Date _date = null;
        ArrayList<Wanted> resultList = new ArrayList<Wanted>();
        try {
            cs = conn.prepareCall("{call sp_FilterWanted(?,?,?,?,?,?,?,?)}", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            
            _date = (Date) parametersMap.get("MinWantedDate");
            cs.setDate("MinWantedDate", new java.sql.Date(_date.getTime()));
            _date = (Date) parametersMap.get("MaxWantedDate");
            cs.setDate("MaxWantedDate", new java.sql.Date(_date.getTime()));
            cs.setInt("MinBountyMoney", Integer.parseInt(parametersMap.get("MinBountyMoney").toString()));
            cs.setInt("MaxBountyMoney", Integer.parseInt(parametersMap.get("MaxBountyMoney").toString()));
            cs.setString("SightingPlace", parametersMap.get("SightingPlace").toString());
            _date = (Date) parametersMap.get("MinSightingTime");
            cs.setDate("MinSightingTime", new java.sql.Date(_date.getTime()));
            _date = (Date) parametersMap.get("MaxSightingTime");
            cs.setDate("MaxSightingTime", new java.sql.Date(_date.getTime()));
            cs.setString("InEffect", parametersMap.get("InEffect").toString());
            
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                rs.beforeFirst();
                while (rs.next()) {
                    int _id = rs.getInt("WantedID");
                    resultList.add(new Wanted(_id));
                }
                return resultList;
            }
        } catch (SQLException ex) {
            Logger.getLogger(WantedController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultList;
    }

    public static ArrayList<Wanted> FilterWantedNotSighted(HashMap parametersMap) {
        CallableStatement cs;
        Date _date = null;
        ArrayList<Wanted> resultList = new ArrayList<Wanted>();
        try {
            cs = conn.prepareCall("{call sp_FilterWantedNotSighted(?,?,?,?,?)}", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            
            _date = (Date) parametersMap.get("MinWantedDate");
            cs.setDate("MinWantedDate", new java.sql.Date(_date.getTime()));
            _date = (Date) parametersMap.get("MaxWantedDate");
            cs.setDate("MaxWantedDate", new java.sql.Date(_date.getTime()));
            cs.setInt("MinBountyMoney", Integer.parseInt(parametersMap.get("MinBountyMoney").toString()));
            cs.setInt("MaxBountyMoney", Integer.parseInt(parametersMap.get("MaxBountyMoney").toString()));
            cs.setString("InEffect", parametersMap.get("InEffect").toString());
            
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                rs.beforeFirst();
                while (rs.next()) {
                    int _id = rs.getInt("WantedID");
                    resultList.add(new Wanted(_id));
                }
                return resultList;
            }
        } catch (SQLException ex) {
            Logger.getLogger(WantedController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultList;
    }
}
